Written by: QCTO Blog
Thursday, February 03, 2011 

• Prior to CT 2.0.0 the exit code was never reflecting errors that occurred -- it always returned zero.
• Prior to CT 2.0.0 the application could show message boxes reflecting issues with the import, which obviously isn't useful in an automated environment.
• The command line of the Windows application isn't easily available from CI software running on other OSs like Linux or Solaris.

However, if you do have access to the Windows application in your CI environment and if you're using CT 2.0.0 or newer, you can make use of the new CT command line interface offered by the new executable QctoCmd.exe, which differs from the "old" command line offered through the GUI executable QuestCodeTesterOracle.exe (or QctoBeta.exe) in the following ways:

• It is a Windows Console application, such that you will no longer get GUI message boxes popping up with error messages.
• The exit code is now non-zero in case of an error, always zero for success. The error codes are documented in the on-line help system.

Example command line for 2.0.0 for importing an XML file (note how you no longer need the /Close parameter that you had to include previous to 2.0.0 in order to close down the application after the operation):

"C:\Program Files (x86)\Quest Software\Quest Code Tester for Oracle 2.0.0 Beta 5\QctoCmd.exe" 
    /u=QCTO200B5 /p=o112 /d=o112 /DBHome=o112_32 /i=C:\MyProject\tests\Q##MY_FUNCTION.xml /po=SCOTT /to=QCTO200B5

which imports the file C:\MyProject\tests\Q##MY_FUNCTION.xml into the repository owned by QCTO200B5, test program generated in the schema QCTO200B5 and program code is owned by SCOTT.

Standard output from the command line shown above could be:

Quest Code Tester for Oracle command line utility. Version 2.0.0.580.

Importing started
----------------------
   C:\MyProject\tests\Q##MY_FUNCTION.xml - SUCCESS
Importing ended
----------------------
Exit code 0

You can use BFILE and DBMS_LOB.LOADCLOBFROMFILE to read a file into a CLOB. Here's an example SQL*Plus script that reads a given file into a CLOB SQL*Plus variable, assuming that you have read access to an Oracle directory MY_PROJECT_TESTS that in turn points to an OS directory like C:\MyProject\tests (Windows) or /user/ci/myproject/tests (Linux, UNIX):

variable xml_import clob

declare
  file       bfile := bfilename('MY_PROJECT_TESTS', 'Q##MY_FUNCTION.xml');
  dst_offset integer := 1;
  src_offset integer := 1;
  lang       integer := 0;
  warn       integer;
begin
  dbms_lob.createtemporary(:xml_import, true, dbms_lob.call);
  dbms_lob.fileopen(file, dbms_lob.file_readonly);
  dbms_lob.loadclobfromfile(
    :xml_import, file, dbms_lob.lobmaxsize,
    dst_offset, src_offset, nls_charset_id('al32utf8'), lang, warn
  );
  dbms_lob.fileclose(file);
  dbms_output.put_line('Read ' || length(:xml_import) || ' character(s)');
end;
/

variable xml_import clob
set linesize 220

declare
  xml_import_binary blob;
  src_offset integer := 1;
  dst_offset integer := 1;
  lang       integer := 0;
  warn       integer;
begin
  dbms_lob.createtemporary(xml_import_binary, true, dbms_lob.call);
  dbms_lob.append(xml_import_binary, hextoraw('3C5143544F5F4558504F52543E0D0A093C212D2D0D0A546F2061766F69642070617273696E67206572726F72732077697468206E657374656420434441544120746167732C2077652068617665207065'));
  ...
  dbms_lob.append(xml_import_binary, hextoraw('3C2F554E49545F54455354533E0D0A09093C2F51555F4841524E4553533E0D0A093C2F544553545F444546494E4954494F4E533E0D0A3C2F5143544F5F4558504F52543E0D0A'));

  dbms_lob.createtemporary(:xml_import, true, dbms_lob.call);
  dbms_lob.converttoclob(
    :xml_import, xml_import_binary, dbms_lob.lobmaxsize,
    dst_offset, src_offset, nls_charset_id('al32utf8'), lang, warn
  );
end;
/

Here's a small Java program that converts a given text file to a SQL*Plus script similar to the one above (written on standard output). The end result is that we have the file represented in the CLOB variable :xml_import:

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;

public class FileToClob {
  /**
   * The hexadecimal characters.
   */
  private static final char HEX_CHARS[] = {
    '0', '1', '2', '3', '4', '5', '6', '7', '8', '9',
    'A', 'B', 'C', 'D', 'E', 'F'
  };
 
  public FileToClob(String filePathName)
    throws FileNotFoundException, IOException {
    InputStream iS = new FileInputStream(filePathName);

    print(
      "variable xml_import clob\n" + 
      "set linesize 220\n" + 
      "\n" + 
      "declare\n" + 
      " xml_import_binary blob;\n" + 
      " src_offset integer := 1;\n" + 
      " dst_offset integer := 1;\n" + 
      " lang       integer := 0;\n" + 
      " warn       integer;\n" + 
      "begin\n" +
      " dbms_lob.createtemporary(xml_import_binary, true, dbms_lob.call);\n"
    );
    
    int n;
    byte[] bytes = new byte[80];
    while ((n = iS.read(bytes)) > 0) {
      print(" dbms_lob.append(xml_import_binary, hextoraw('");
      for (int i = 0; i < n; i++) {
        print("" + HEX_CHARS[(bytes[i] & 0xF0) >> 4] + HEX_CHARS[bytes[i] & 0x0F]);
      }
      print("'));\n");
    }
    iS.close();
    
    print("\n");
    print(
      " dbms_lob.createtemporary(:xml_import, true, dbms_lob.call);\n" +
      " dbms_lob.converttoclob(\n" +
      "    :xml_import, xml_import_binary, dbms_lob.lobmaxsize,\n" +
      "    dst_offset, src_offset, nls_charset_id('al32utf8'), lang, warn\n" +
      " );\n" +
      "end;\n" +
      "/"
    );
  }

  private static void print(String s) {
    System.out.print(s);
  }
 
  public static void main(String[] args)
    throws FileNotFoundException, IOException {
    FileToClob fileToClob = new FileToClob(args[0]);
  }
}

Calling Import PL/SQL API

Now we have read the XML file into our :xml_import CLOB variable, we can simply call the CT PL/SQL API for importing this CLOB. This is divided into 4 parts: Parsing the XML, setting import options (2 parts) and importing:

begin
  -- Load the export, preparing for import.
  qu_xmldom_import.init_xml(:xml_import);
  -- Set options for the import.
  qu_xmldom_import.set_options(
    include_results_in => false,
    include_program_source_in => false,
    tdg_conflict_handling_in => qu_xmldom_import.tdg_skip,
    td_merge_in => false,
    harness_guid_for_merge_in => null,
    skip_new_test_cases_in => null
  );
  -- Set program and test code owner.
  qu_xmldom_import.set_for_mapping(
    prog_owner_in => 'SCOTT',
    harn_owner_in => USER
  );
  -- Perform the import.
  qu_xmldom_import.import_as_xml;
end;
/

This example assumes that your program owner is 'SCOTT'. The last API call performs an implicit commit. Exceptions can be raised by any of these calls, eg:

ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00225: end-element tag "QCTO_EXPORT_HEADER" does not match start-element tag "QCTO_EXPORT_HEADERx"
Error at line 19
ORA-06512: at "XDB.DBMS_XSLPROCESSOR", line 974
ORA-06512: at "XDB.DBMS_XSLPROCESSOR", line 1002
ORA-06512: at "QCTO200B5.QU_XMLDOM_IMPORT", line 554
ORA-06512: at "QCTO200B5.QU_XMLDOM_IMPORT", line 576
ORA-06512: at "QCTO200B5.QU_XMLDOM_IMPORT", line 914
ORA-06512: at "QCTO200B5.QU_XMLDOM_IMPORT", line 974*
ORA-06512: at line 3

There are two ways of accomplishing this:

• Calling CT with specific command line options: /T=<test_definition_name> and /Delete. You need to be aware that the exit code of the application is non-zero if the TD isn't found but that's to be expected as you get the same when trying to delete a file in the file system with an OS command.
• Using one of the PL/SQL APIs provided:
  • Find the UNIVERSAL_ID in QU_HARNESS table for applicable NAME/TEST_NAME/PROGRAM_OWNER/PROGRAM_NAME and then call the following procedure in QU_HARNESS_XP:

procedure del(
  universal_id_in   in qu_harness_tp.universal_id_t,
  rows_out          out pls_integer,
  handle_error_in   in boolean := true,
  del_test_pkg_in   in boolean default false,
  part_of_import_in in boolean default false
);

• Use another of the DEL or DEL_% subprograms in QU_HARNESS_XP with parameters appropriate for your requirements.

However, I would argue that it's not necessary to delete the TD specifically, since -- in my view -- the XML file containing the TD (produced by exporting it from CT) must always reflect the current state of the TD in your revision control system, such that you:

• Avoid problems when somebody manually deploys it to a given environment.
• Don't have to program specific rules into your CI scripts for handling removal of TDs -- you can always deploy the latest version of any given TD XML (unless you start the CI deployment by removing all TDs in a "pre burner" step).

You can remove all the TDs in a given repository through the following anonymous block:

declare
  n pls_integer;
begin
  for td in (
        select universal_id,
               name,
               test_name
        from   qu_harness
        where name != 'IMPLICIT_'
      ) loop
    begin
      dbms_output.put_line('Deleting TD "' || td.test_name || '"...');
      qu_harness_xp.del(
        universal_id_in => td.universal_id,
        rows_out => n,
        handle_error_in => true,
        del_test_pkg_in => true,
        part_of_import_in => false
      );
      dbms_output.put_line('Done. ' || n || ' row(s) deleted.');
    exception
      when others then
        dbms_output.put_line(sqlerrm);
    end;
  end loop;

  commit;
end;
/

 (omit using TEST_NAME if you're using CT 1.9.1 or earlier as that's a new feature of CT 2.0.0).

• Edit the XML file and remove all TEST_CASES fragments.
• Use Test Editor to remove all test cases and export again.
• Use Test Builder to remove all test cases and export again.

You then need to commit/check in the new XML file to your revision control system.

Trackback Print

Tags: code tester

Categories: Code Tester for Oracle

Location: Blogs Real Automated Code Testing for Oracle

Continuous Integration Part 2

http://www.toadworld.com/Blogs/tabid/67/EntryId/651/Continuous-Integration-Part-2.aspx